﻿CREATE PROCEDURE [dbo].[ListUsers]
@UserID INT, @RoleID INT, @OnlyActive BIT=0
AS
IF @RoleID = 1
	BEGIN
	  SELECT 
		UserID, 
		UserName, 
		Users.RoleID,
		Roles.Name 'RoleName',
		Users.UserTypeID,
		Users.ExpectedHours,
		Users.IsActive
	  FROM Users INNER JOIN Roles ON Users.RoleID = Roles.RoleID
	  WHERE ((@OnlyActive = 0) OR Users.IsActive = 1)
		ORDER BY UserName
	END
ELSE IF @RoleID = 2
	BEGIN
	  SELECT DISTINCT
		Users.UserID,
		Users.UserName,
		Users.RoleID,
		Roles.Name 'RoleName',
		Users.UserTypeID,
		Users.ExpectedHours,
		Users.IsActive
	  FROM Users 
	  INNER JOIN ProjectMembers ON Users.UserID=ProjectMembers.UserID
	  INNER JOIN Projects ON ProjectMembers.ProjectID=Projects.ProjectID
	  INNER JOIN Roles ON Users.RoleID = Roles.RoleID
	  WHERE	@UserID = Projects.ManagerUserID OR @UserID = Users.UserID
		AND ((@OnlyActive = 0) OR Users.IsActive = 1)
		ORDER BY Users.UserName

	END
ELSE
	SELECT
		UserID, 
		UserName, 
		Users.RoleID,
		Roles.Name 'RoleName',
		Users.UserTypeID,
		Users.ExpectedHours,
		Users.IsActive
	FROM 
		Users INNER JOIN Roles ON Users.RoleID = Roles.RoleID
	WHERE UserID = @UserID
		AND ((@OnlyActive = 0) OR Users.IsActive = 1)
		ORDER BY UserName

